package com.stx.bms.dao;

import com.stx.bms.entity.Blog;
import com.stx.bms.util.JDBCUtil;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

public class BlogDAOImpl implements BlogDAO {

    //新增
    public void add(String title, String content, int userId, String imgUrl){

        String sql = "insert into t_bms_blog values(seq_bms_blog.nextval, '"
                + title + "','" + content + "',sysdate," + userId+ ", '"
                + imgUrl + "')";

        innerExecuteSql(sql);
    }

    private void innerExecuteSql(String sql){
        Connection con = null;
        Statement st = null;
        try {
            con = JDBCUtil.getCon();
            st = con.createStatement();
            st.execute(sql);
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JDBCUtil.close(con, st);
        }
    }

    //删除
    public void delete(int id){
        String sql="delete from t_bms_blog where id=" + id;
        innerExecuteSql(sql);
    }
    //修改
    public void update(int id, String newTitle, String newContent){
        String sql = "update t_bms_blog set title='"+newTitle
                +"', content='" + newContent + "' where id="+id;

        innerExecuteSql(sql);
    }

    //查看
    public List<Blog> viewAll(){
        //创建一个装Blog对象的List容器
        List<Blog> blogList = new ArrayList();


        String sql = "select b.id bid, title, imgUrl, b.createtime btime, username" +
                " from t_bms_blog b, t_bms_user u " +
                " where b.userid=u.id order by b.createTime desc";

        Connection con =  null;
        Statement st = null;
        ResultSet rs = null;
        try{
            con =JDBCUtil.getCon();
            st = con.createStatement();

            rs = st.executeQuery(sql);
            while(rs.next()){
                //一旦获取到一条数据就创建一个Blog对象
                Blog blog = new Blog();
                //将该条数据的列设置到Blog对象中封装起来
                blog.setId( rs.getInt("bid") );
                blog.setTitle( rs.getString("title") );
                blog.setCreateTime( rs.getTimestamp("btime") );

                blog.setUsername( rs.getString("username") );
                blog.setImgUrl( rs.getString("imgUrl") );

                //将Blog对象放进List容器
                blogList.add(blog);
            }
        }catch (SQLException e){
            e.printStackTrace();
        }finally {
            JDBCUtil.close(con, st, rs);
        }
        //返回List容器，里面放的就是查询出来的所有Blog数据
        return blogList;
    }

    public Blog viewById(int id){
        String sql = "select * from t_bms_blog where id=" + id;

        Connection con = null;
        Statement st = null;
        ResultSet rs = null;

        try{
            con = JDBCUtil.getCon();
            st = con.createStatement();
            rs = st.executeQuery(sql);
            if(rs.next()){
                //查到ID为指定ID的博客信息
                Blog blog = new Blog();
                blog.setId( rs.getInt("id") );
                blog.setTitle( rs.getString("title") );
                blog.setContent( rs.getString("content") );
                return blog;
            }
        }catch (SQLException e){
            e.printStackTrace();
        }finally {
            JDBCUtil.close(con, st, rs);
        }
        return null;
    }

}